const msqlFile = require('../assets/js/commnMySQL');
const connection = msqlFile.sqlConnection;
module.exports = {
   getPostsAll(callback){
     let sql = `SELECT posts.id,title,posts.\`status\`,created,nickname,\`name\`
     FROM posts 
     JOIN users ON posts.user_id=users.id
     JOIN categories ON posts.category_id=categories.id`;
    connection.query(sql,(err,result)=>{
      callback(err,result);
    });
   },
   getPostsPage(pageIndex,pageSize,callback){
     let sql = `SELECT posts.id,title,posts.\`status\`,created,nickname,\`name\`
     FROM posts 
     JOIN users ON posts.user_id=users.id
     JOIN categories ON posts.category_id=categories.id
    LIMIT ${(pageIndex-1)*pageSize},${pageSize}`;
    connection.query(sql,(err,result)=>{
      callback(err,result);
    });
   },
   delpostsById(id,callback){
     let sql = `DELETE FROM posts WHERE id=${id}`;
     connection.query(sql,(err,result)=>{
       callback(err,result);
     });
   },
   getPostsCount(callback){
     let sql = `SELECT COUNT(*) as total FROM posts`;
     connection.query(sql,(err,result)=>{
       callback(err,result);
     });
   },
   getPostsByFilter(sql,callback){
     connection.query(sql,(err,result)=>{
       callback(err,result);
     });
   },
   getPostsCountAll(condition,callback){
    let sql = `SELECT COUNT(*) as total FROM posts` + condition;
    connection.query(sql,(err,result)=>{
      callback(err,result);
    });
  },
  newPostAdd(data,callback){
    let sql = `INSERT INTO posts SET ?`;
    connection.query(sql,data,(err,result)=>{
      callback(err,result);
    });
  },
  getPostsById(id,callback){
    let sql = `SELECT * FROM posts WHERE id=${id}`;
    connection.query(sql,(err,result)=>{
      callback(err,result);
    });
  },
  editPostsData(id,data,callback){
    let sql = `UPDATE posts set ? WHERE id=${id}`;
    connection.query(sql,data,(err,result)=>{
      callback(err,result);
    });
  },
  newestPosts(callback){
    let sql = `SELECT 
    posts.id,title,content,created,feature,views,likes,
    users.nickname,
    categories.\`name\`
     FROM posts
    JOIN categories ON posts.category_id=categories.id
    JOIN users ON posts.user_id=users.id
    ORDER BY created DESC
    LIMIT 5;`
    connection.query(sql,(err,result)=>{
      callback(err,result);
    });
  },
  getcategoriPost(id,pageIndex,pageSize,callback){
    let offset = (pageIndex-1)*pageSize;
    let sql = `SELECT 
    posts.id,title,content,created,feature,views,likes,
    users.nickname,
    categories.\`name\`
     FROM posts
    JOIN categories ON posts.category_id=categories.id
    JOIN users ON posts.user_id=users.id
    WHERE category_id=${id}
    LIMIT ${offset},${pageSize}`;
    connection.query(sql,(err,result)=>{
      callback(err,result);
    });
  },
  getdetailDataById(id,callback){
    let sql = `SELECT 
    posts.id,categories.\`name\`,categories.id as category_id,title,content,created,views,users.nickname
     FROM posts
    JOIN categories ON posts.category_id=categories.id
    JOIN users ON posts.user_id=users.id
    WHERE posts.id=${id}`;
    connection.query(sql,(err,result)=>{
      callback(err,result);
    });
  }
};